﻿
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Drawing;
using DocumentFormat.OpenXml.Drawing.Spreadsheet;
using DocumentFormat.OpenXml.Office.Word.Y2020.OEmbed;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using OpenMcdf;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using static System.Net.Mime.MediaTypeNames;

namespace UnitTest.Excel
{
    public class PictureInfo
    {
        public int FromRow { get; set; }
        public int FromCol { get; set; }
        public byte[] Buffer { get; set; }
    }
    public class TestExcel
    {
        public void WriteExcel()
        {
            string fullPath = @"C:\Users\dell\Desktop\API配置连接\测试添加图片.xlsx";
            DataTable dataTable = new DataTable();
           dataTable.Columns.Add(new DataColumn("测试列1"));
           dataTable.Columns.Add(new DataColumn("测试列2"));
            dataTable.Columns.Add(new DataColumn("测试列3"));
            DataRow dataRow = dataTable.NewRow();
            dataRow["测试列1"] = "这是111111111";
            dataRow["测试列3"] = "这是2222";
            dataRow["测试列2"] = @"D:\测试图片\111.jpg";

            dataTable.Rows.Add(dataRow);


            DataRow dataRow1 = dataTable.NewRow();
            dataRow1["测试列1"] = "这是2222222222";
            dataRow1["测试列3"] = "这是2222";
            dataRow1["测试列2"] = @"D:\2222.jpg";

            dataTable.Rows.Add(dataRow1);

            DataRow dataRow2 = dataTable.NewRow();
           dataRow2["测试列1"] = "这是3333333333";
            dataRow2["测试列3"] = "这是2222";
           dataRow2["测试列2"] = @"D:\5555.jpg";

            dataTable.Rows.Add(dataRow2);

            DataService.Office.Excel.ExcelHepler.ExportToExcel(dataTable,fullPath);
        }
        public void ReadFiles()
        {
            List<PictureInfo> pictures = null;
            using (SpreadsheetDocument document = SpreadsheetDocument.Open(@"excel路径 ", true))
            {
                #region 第一种方式
                WorkbookPart workbookPart = document.WorkbookPart;
                var worksheetParts = workbookPart.WorksheetParts;
                foreach (var itemwp in worksheetParts)
                {
                    var OleObjects = itemwp.Worksheet.Descendants<OleObjects>();

                    foreach (OleObjects item in OleObjects)
                    {

                        var choices = item.Descendants<AlternateContentChoice>();
                        foreach (AlternateContentChoice choice in choices)
                        {
                            var objs = choice.Descendants<OleObject>();
                            foreach (OleObject itemOleObject in objs)
                            {
                                var part1 = itemwp.GetPartById(itemOleObject.Id);

                                string fileName = Guid.NewGuid().ToString().Replace("-", "");
                                string filePath = @"C:\Users\dell\Desktop\" + fileName + ".pdf";

                                using (var fileStream = File.OpenWrite(filePath))
                                {
                                    using (var stream = part1.GetStream(FileMode.Open, FileAccess.Read))
                                    {
                                        stream.CopyTo(fileStream);
                                        //  StreamToFile(stream, filePath);
                                    }
                                }




                                Console.WriteLine(itemOleObject.OuterXml);
                                Console.WriteLine(itemOleObject.Id + "呵呵啊啊" + itemOleObject.NamespaceUri + "相对路径：" + part1.Uri);
                            }
                        }


                    }
                }
                #endregion




                WorkbookPart wbPart = document.WorkbookPart;
                var sheets = wbPart.Workbook.Sheets.Take(1);
                foreach (Sheet sheet in sheets)
                {
                    Console.WriteLine(sheet.Name);
                    WorksheetPart wsPart = (WorksheetPart)wbPart.GetPartById(sheet.Id);

                    var dddd = wsPart.Worksheet.Descendants<OleObjects>();
                    foreach (OleObjects item in dddd)
                    {
                        var choices = item.Descendants<AlternateContentChoice>();
                        foreach (var choice in choices)
                        {
                            var objs = choice.Descendants<OleObject>();
                            foreach (OleObject itemOleObject in objs)
                            {
                                var part1 = wsPart.GetPartById(itemOleObject.Id);

                                #region 先复制到本地

                                var tempFolder = System.IO.Path.GetTempPath();
                                var tempFileNam = System.IO.Path.GetRandomFileName();
                                var oleFile = System.IO.Path.Combine(tempFolder, tempFileNam);
                                Console.WriteLine(oleFile + "***" + itemOleObject.ProgId + "&&&&&" + part1.ContentType);
                                oleFile = @"C:\Users\dell\Desktop\1111111.bin";
                                using (var fileStream = File.OpenWrite(oleFile))
                                {
                                    using (var stream = part1.GetStream(FileMode.Open))
                                    {
                                        stream.CopyTo(fileStream);
                                    }
                                }
                                #endregion
                                //打开文件
                                //                                Root Entry
                                //CompObj
                                //ObjInfo
                                //Ole10Native
                                var compoundFile = new CompoundFile(oleFile);


                                Console.WriteLine(compoundFile.GetNameDirEntry(0));
                                //Console.WriteLine(compoundFile.GetNameDirEntry(1));
                                //Console.WriteLine(compoundFile.GetNameDirEntry(2));
                                //Console.WriteLine(compoundFile.GetNameDirEntry(3));
                                //Console.WriteLine(compoundFile.GetNameDirEntry(4));
                                string fileName = "ffffffffffff";
                                string filePath = @"C:\Users\dell\Desktop\" + fileName + ".pdf";
                                var packgeStream = compoundFile.RootStorage.GetStream(fileName + ".pdf");



                                compoundFile.SaveAs(filePath);
                                using (var pdfFileStream = File.OpenWrite(filePath))
                                {
                                    pdfFileStream.Write(packgeStream.GetData().AsSpan());
                                }



                                Console.WriteLine(itemOleObject.Id + "嘻嘻" + itemOleObject.NamespaceUri + "哈哈哈：" + part1.Uri);
                            }
                        }
                    }
                    var drawingPart = wsPart.DrawingsPart;
                    // DrawingsPart drawingPart = wsPart.GetPartsOfType<DrawingsPart>().ToList().FirstOrDefault();
                    pictures = new List<PictureInfo>();
                    if (drawingPart != null)
                    {
                        foreach (var part in drawingPart.Parts)
                        {
                            PictureInfo pic = new PictureInfo();
                            var part1 = document.GetPartById(part.RelationshipId);
                            var stream = part1.GetStream();
                            pic.Buffer = StreamToBytes(stream);
                            pictures.Add(pic);
                            StreamToFile(stream, @"C:\Users\dell\Desktop\ddddd.jpg");
                            // ImagePart imgPart = (ImagePart)part.OpenXmlPart;
                        }

                        //var worksheetDrawings = drawingPart.WorksheetDrawing.Where(c => c.ChildElements.Any
                        //    (a => a.GetType().FullName == "DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture")).ToList();
                        //foreach (var worksheetDrawing in worksheetDrawings)
                        //{
                        //    if (worksheetDrawing.GetType().FullName ==
                        //        "DocumentFormat.OpenXml.Drawing.Spreadsheet.TwoCellAnchor")
                        //    {
                        //        TwoCellAnchor anchor = (TwoCellAnchor)worksheetDrawing;
                        //        DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture picDef =
                        //            (DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture)
                        //            anchor.ChildElements.FirstOrDefault(c => c.GetType().FullName ==
                        //            "DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture");
                        //        if (picDef != null)
                        //        {
                        //            var embed = picDef.BlipFill.Blip.Embed;
                        //            if (embed != null)
                        //            {
                        //                var picMapping = pictures.FirstOrDefault(c => c.RefId == embed.InnerText);
                        //                picMapping.FromCol = int.Parse(anchor.FromMarker.ColumnId.InnerText);
                        //                picMapping.FromRow = int.Parse(anchor.FromMarker.RowId.InnerText);
                        //            }
                        //        }
                        //        // anchor.FromMarker.RowId + anchor.FromMarker.ColumnId 
                        //    }
                        //}
                    }

                }
            }
            //把图片信息显示在DataGridView中
            //var pic1 = pictures.OrderBy(c => c.FromCol).OrderBy(c => c.FromRow).ToList();
            //dataGridView1.AutoGenerateColumns = true;

            //dataGridView1.DataSource = pic1;
            Console.WriteLine("完成");
        }

        public void ReadFiles_1()
        {


        }


        public byte[] StreamToBytes(Stream stream)

        {

            byte[] bytes = new byte[stream.Length];

            stream.Read(bytes, 0, bytes.Length);

            // 设置当前流的位置为流的开始

            stream.Seek(0, SeekOrigin.Begin);

            return bytes;

        }

        /// <summary>

        /// 将 byte[] 转成 Stream

        /// </summary>

        public Stream BytesToStream(byte[] bytes)

        {
            Stream stream = new MemoryStream(bytes);

            return stream;

        }

        /// <summary>

        /// 将 Stream 写入文件

        /// </summary>

        public void StreamToFile(Stream stream, string fileName)
        {

            // 把 Stream 转换成 byte[]

            byte[] bytes = new byte[stream.Length];

            stream.Read(bytes, 0, bytes.Length);

            // 设置当前流的位置为流的开始

            stream.Seek(0, SeekOrigin.Begin);

            // 把 byte[] 写入文件

            FileStream fs = new FileStream(fileName, FileMode.Create);

            BinaryWriter bw = new BinaryWriter(fs);

            bw.Write(bytes);

            bw.Close();

            fs.Close();

        }
    }
}
